<#escape x as (x)!"">
package ${basePackage}.${plate}.${modulePackage}.dto;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import ${basePackage}.utils.xss.SQLFilter;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;

import com.fasterxml.jackson.annotation.JsonFormat;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

    /**
    * @公司:${corpName}
    * @功能:${functionCn} Model
    * @作者:${authName}
    * @日期:${datetime}
    * @版本:1.0
    * @修改:
    */ 

public class ${className}Query {

	private boolean timeChecked = false;
	private String startTime;
	private String endTime;
	
	private int page;
	private int limit = 10000;

	private List<Opt> criterias;
	
	public ${className}Query() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public boolean isTimeChecked() {
		return timeChecked;
	}

	public void setTimeChecked(boolean timeChecked) {
		this.timeChecked = timeChecked;
	}

	public String getStartTime() {
		return startTime;
	}

	public void setStartTime(String startTime) {
		this.startTime = startTime;
	}

	public String getEndTime() {
		return endTime;
	}

	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}
	
	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}

	public int getLimit() {
		return limit;
	}

	public void setLimit(int limit) {
		this.limit = limit;
	}

	public List<Opt> getCriterias() {
		return criterias;
	}

	public void setCriterias(List<Opt> criterias) {
		this.criterias = criterias;
	}
	

	// 为省份的生成sql 按省过滤
	public String toSqlString(int provinceId) {

		String sql_select_base = " ";

		List<String> list_select = new ArrayList<String>();
		Set<String> list_from = new HashSet<String>();
		List<String> list_where = new ArrayList<String>();

		String table = "MO_AS_MSG_";

		list_from.add(table + " a");

		if (timeChecked) {
			if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
				list_where.add(" a.committime BETWEEN to_date( '" + startTime
						+ "', 'YYYY-MM-DD HH24:MI:SS' ) AND to_date( '" + endTime + "', 'YYYY-MM-DD HH24:MI:SS' )");
			}
		}

		// 添加分省处理
		if (provinceId > 0) {
			// 增加分省的表
			list_from.add("sp_basic_info_attach c");
			list_from.add("areacode f");
			// 增加分省条件
			list_where.add("a.servicenumber = c.sevicenumber ( + )");
			list_where.add("c.areacode = f.codeid");
			list_where.add("f.PROVINCE_ID = " + provinceId);
		}

		List<String> list_or_where = new ArrayList<String>();
		// 动态条件处理
		for (Opt opt : this.criterias) {
			boolean isfisrt = true;
			StringBuffer c_sql = new StringBuffer();
			for (Criteria c : opt.getOptList()) {
				if (StringUtils.isBlank(c.getColumn()) || c.getOperator() == null || c.isDel()) {
					continue;
				}
				if (StringUtils.isBlank(c.getValue()) && c.getOperator() != 8) {
					continue;
				}

				if (!isfisrt) {
					c_sql.append(c.getOpt() + " ");
				} else {
					isfisrt = false;
				}

				c_sql.append(c.getColumn() + " ");

				if (c.getOperator() == 1) {
					c_sql.append("= '" + c.getValue() + "' ");
				}
				if (c.getOperator() == 2) {
					c_sql.append("<> '" + c.getValue() + "' ");
				}
				if (c.getOperator() == 3) {
					c_sql.append("< '" + c.getValue() + "' ");
				}
				if (c.getOperator() == 4) {
					c_sql.append("> '" + c.getValue() + "' ");
				}
				if (c.getOperator() == 5) {
					c_sql.append(">= '" + c.getValue() + "' ");
				}
				if (c.getOperator() == 6) {
					c_sql.append("<= '" + c.getValue() + "' ");
				}
				if (c.getOperator() == 7) {
					c_sql.append("like '%" + c.getValue() + "% ");
				}
				if (c.getOperator() == 8) {
					c_sql.append("is null ");
				}
			}

			if ("and".equals(opt.getOpt()) && !isfisrt) {
				list_where.add("(" + c_sql.toString() + ")");
			}
			if ("or".equals(opt.getOpt()) && !isfisrt) {
				list_or_where.add("(" + c_sql.toString() + ")");
			}
		}

		StringBuffer sb_sql = new StringBuffer();
		StringBuffer sb_select = new StringBuffer();
		StringBuffer sb_from = new StringBuffer();
		StringBuffer sb_where = new StringBuffer();

		int i = 0;
		for (String select : list_select) {
			i++;
			sb_select.append("\t");
			sb_select.append(select);
			sb_select.append(",");
			sb_select.append("\n");
		}

		i = 0;
		for (String from : list_from) {
			i++;
			sb_from.append("\t");
			sb_from.append(from);
			if (i != list_from.size()) {
				sb_from.append(",");
			}
			sb_from.append("\n");
		}

		i = 0;
		boolean iswhere = false;
		for (String where : list_where) {
			i++;
			sb_where.append("\t");
			if (iswhere) {
				sb_where.append("and ");
			}
			iswhere = true;
			sb_where.append(where);
			sb_where.append("\n");
		}
		i = 0;
		for (String where : list_or_where) {
			i++;
			sb_select.append("\t");
			if (iswhere) {
				sb_where.append("or ");
			}
			iswhere = true;
			sb_where.append(where);
			sb_where.append("\n");
		}

		sb_sql.append("select \n");
		sb_sql.append("" + sb_select.toString().toLowerCase());
		sb_sql.append(sql_select_base.toLowerCase() + "\n");
		sb_sql.append("from \n");
		sb_sql.append("" + sb_from.toString().toLowerCase() + "");
		if (sb_where.length() > 0) {
			sb_sql.append("where \n");
			sb_sql.append("" + sb_where.toString() + "");
		}
		return sb_sql.toString();
	}

	// 为省份的生成sql 按省过滤
	public Map toSqlMap(int provinceId) {
		Map map = new HashMap();
		map.put("a", true);
		map.put("c", false);
		map.put("f", false);

		map.put("page", page);
		map.put("limit", limit);

		map.put("timeChecked", timeChecked);
		if (timeChecked) {
			if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
				map.put("startTime", startTime);
				map.put("endTime", endTime);
			} else {
				map.put("timeChecked", false);
			}
		}

		SimpleDateFormat sdf = new SimpleDateFormat("dd");

		String table = "MO_AS_MSG_";

		Set<String> list_from = new HashSet<String>();
		List<String> list_where = new ArrayList<String>();
		List<String> list_or_where = new ArrayList<String>();

		list_from.add(table + " a");

		// 分省处理
		if (provinceId > 0) {
			// map.put("d", true);
			map.put("c", true);
			map.put("f", true);
			list_from.add("sp_basic_info_attach c");
			list_from.add("areacode f");
		}

		List<LogicalOpt> list_lopts = new ArrayList();

		// 动态条件处理
		for (Opt opt : this.criterias) {
			boolean isfisrt = true;
			List<Criteria> list_cris = new ArrayList();
			for (Criteria c : opt.getOptList()) {
				if (StringUtils.isBlank(c.getColumn()) || c.getOperator() == null || c.isDel()) {
					continue;
				}
				if (StringUtils.isBlank(c.getValue()) && c.getOperator() != 8) {
					continue;
				}
				String column = SQLFilter.sqlInject(c.getColumn());
				String value = SQLFilter.sqlInject(c.getValue());
				c.setColumn(column);
				c.setValue(value);
				isfisrt = false;
				list_cris.add(c);
			}
			if (!opt.isDel() && !isfisrt) {
				LogicalOpt lopt = new LogicalOpt();

				if ("and".equals(opt.getOpt())) {
					lopt.setOpt(1);
				} else {
					lopt.setOpt(2);
				}

				lopt.setCriList(list_cris);

				list_lopts.add(lopt);
			}
		}
		map.put("list_from", list_from);
		map.put("list_where", list_where);
		map.put("list_or_where", list_or_where);
		map.put("list_lopts", list_lopts);

		return map;
	}

	
}
</#escape>